﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using sscore;
using sscore.Utils;
using DM4Script;

namespace DM4
{
    public partial class DM4
    {
        private const string STR_FailedToSetPropertyInTaskOn = "Failed to set property in TaskOn";
        private const string STR_ErrorCommand = "Error command!!";
        private const string STR_AllTasksOnCompleted = "All TasksOn completed";
        private const string STR_AllTasksCompleted = "All Tasks completed";
        private const string STR_ErrorUpdateTableRun_script_header = "Error update table [run_script_header]";
        private const string STR_IdScrIdAndModuleCodeIsNULL = "Id scrId and module code is NULL";
        private const string STR_ParametersIsBad = "Error, function 'RunScript', parameters is bad";

        private struct ParamForRun
        {
            public readonly int UserId;
            public readonly int ScriptId;
            public readonly string SessionId;
            public readonly string ModCode;
            public readonly string ModParam;

            public ParamForRun(int userId, int scriptId, string sessionId, string modCode, string modParams)
            {
                UserId = userId;
                ScriptId = scriptId;
                SessionId = sessionId;
                ModCode = modCode;
                ModParam = modParams;
            }
        }
        
        private bool ResumeScripts()
        {
            BeginFunction("ResumeScripts");

            //WriteLog("test log", MessageType.Error);

            List<ParamForRun> paramForRuns = new List<ParamForRun>();

            const string select = "SELECT [rsh_id],[rsh_sessions_id],[rsh_script_name],[rsh_user_id] FROM [run_script_header] WITH (nolock) WHERE [rsh_status] <> @rsh_status";

            try
            {
                SqlCommand command = new SqlCommand(select, _conn);
                command.Parameters.AddWithValue("@rsh_status", RshStatus.Complete);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                     while (reader.Read())
                     {
                         long rshId = (long)reader["rsh_id"];
                         int userId = (int)reader["rsh_user_id"];
                         int scriptId = DM4Consts.DefaultNULLId;
                         string sessionId = (string)reader["rsh_sessions_id"];
                         string modCode = "";
                         string modParams = "";

                         string rshScriptName = (string)reader["rsh_script_name"];

                         Regex reg = new Regex(String.Format("^{0}_(.+)$", DM4Consts.DefaultGenerateSctript));

                         if (reg.IsMatch(rshScriptName))
                         {
                             modCode = reg.Match(rshScriptName).Groups[1].Value;
                             modParams = GetTaskIdParam(rshId, DM4Consts.Taskdefault);
                         }
                         else
                             scriptId = GetScriptsId(rshScriptName);

                         paramForRuns.Add(new ParamForRun(userId, scriptId, sessionId, modCode, modParams));
                     }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            foreach (ParamForRun paramForRun in paramForRuns)
            {
                OperationResult op = RunScript(paramForRun.UserId, paramForRun.ScriptId, paramForRun.SessionId, new string[]{paramForRun.ModCode},
                          paramForRun.ModParam);
                if (op.Result != DM4Consts.Ok)
                {
                    WriteLog(op.Message, MessageType.Error);
                    return false;
                }
            }

            EndFunction("ResumeScripts");

            return true;
        }

        public OperationResult RunScript(string usrSessionId, int scrId = DM4Consts.DefaultNULLId, string rshSessionId = null, string[] modCode = null,
                                         string modParams = null)
        {
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = RunScript(userIdOpResult.Result, scrId, rshSessionId, modCode, modParams);
            result.UsrSessionId = userIdOpResult.UsrSessionId;

            return result;
        }

        private OperationResult RunScript(int userId, int scrId = DM4Consts.DefaultNULLId, string rshSessionId = null, string[] modCode = null,
                                         string modParams = null)
        {
            BeginFunction("RunScript");

            OperationResult result = new OperationResult(DM4Consts.Ok);

            try
            {
                ScriptParse script = new ScriptParse();

                #region 1. зачитать скрипт по 'scrId' или сформировать Default + 'modCode'
                //get script text
                if (scrId != DM4Consts.DefaultNULLId)
                {
                    string[] nameText = GetScriptsNameText(scrId).Split(new[] { '@' }, 2);
                    if (nameText.Count() == 2)
                    {
                        script.ScriptName = nameText[0];
                        script.ScriptTxt = nameText[1];
                    }
                }
                else if (modCode != null)
                {
                    if (modCode.Count() == 1)
                    {
                        script.ScriptName = String.Format("{0}_{1}", DM4Consts.DefaultGenerateSctript, modCode[0]);
                        script.ScriptTxt = String.Format(ScriptConst.ScriptDefault, modCode[0], DM4Consts.DefaultSessionCount, DM4Consts.DefaultTaskPriority);
                    }
                    else
                    {
                        script.ScriptName = String.Format("{0}", DM4Consts.DefaultGenerateSctriptGroup);
                        List<string> declaretask = new List<string>(from mod in modCode select String.Format(ScriptConst.ScriptDefaultDeclareGroup, mod, DM4Consts.DefaultSessionCount, DM4Consts.DefaultTaskPriority));
                        List<string> ontask = new List<string>(from mod in modCode select String.Format(ScriptConst.ScriptDefaultOnGroup, mod));
                        script.ScriptTxt = String.Format(ScriptConst.ScriptDefaultGroup, String.Join("\n", declaretask), String.Join("\n", ontask));
                    }
                }
                else
                {
                    result.Result = DM4Consts.ErrorDefaultGenerateSctript;
                    result.Message = STR_IdScrIdAndModuleCodeIsNULL;
                    return result;
                }

                if(String.IsNullOrEmpty(rshSessionId))
                    rshSessionId = GenerateEntityId(script.ScriptName);

                if (String.IsNullOrEmpty(script.ScriptName) || String.IsNullOrEmpty(script.ScriptTxt))
                {
                    result.Result = DM4Consts.ErrorSQL;
                    result.Message = String.Format("An error in the script id: {0} or module: {1}", scrId, modCode);
                    return result;
                }
                #endregion
                
                #region 2. анализ скрипта
                //analize
                if (!script.Analize())
                {
                    result.Result = DM4Consts.ErrorAnalizeScript;
                    result.Message = String.Format("An error in the script name: {0}{1}{2}", script.ScriptName, Environment.NewLine, script.GetError());
                    return result;
                }
                #endregion
                
                #region 3. формирование параметров и добавление до основных
                if (!String.IsNullOrEmpty(modParams))
                {
                    Dictionary<string, ParamEntry> paramAdd = StringUtils.StringToParams(modParams);
                    if(paramAdd.Count == 0)
                    {
                        result.Result = DM4Consts.ErrorParameters;
                        result.Message = STR_ParametersIsBad;
                        return result;
                    }

                    foreach (Prototypes.ScriptTask task in from tasks in script.Declare.Tasks select tasks.Value)
                    {
                        foreach (var param in paramAdd)
                        {
                            Dictionary<string, ParamEntry> paramToAdd = ScriptConst.taskParam.Contains(param.Key) ? task.Params : task.SubParams;
                            if (paramToAdd.ContainsKey(param.Key))
                                paramToAdd[param.Key] = param.Value;
                            else
                                paramToAdd.Add(param.Key, param.Value);
                        }
                    }
                }
                #endregion
                
                #region 4. заполнение поля ScriptId в ScriptTask
                foreach (Prototypes.ScriptTask task in from tasks in script.Declare.Tasks select tasks.Value)
                {
                    task.ScrId = scrId;
                    task.RshSessionId = rshSessionId;
                    task.UsrId = userId;
                }
                #endregion

                #region 5. проверка наличия модулей для запуска
                //test exist modules
                foreach (Prototypes.ScriptTask task in from tasks in script.Declare.Tasks select tasks.Value)
                {
                    if(!ModuleExists(task.ModuleName))
                    {
                        result.Result = DM4Consts.ErrorModuleNotExists;
                        result.Message = String.Format("Module not exists: {0}", task.ModuleName);
                        return result;
                    }
                }
                #endregion
                
                #region 6. проверка записи в таблице [run_script_header] по 'rshSessionId', если есть то уже было запущено + зачитать параметры
                //test exists in tamle [run_script_header]
                script.RshId = GetIdRunScriptHeader(rshSessionId);
                if (script.RshId == DM4Consts.DefaultNULLId)
                {
                    script.RshId = InsertRunScriptHeader(script.ScriptName, rshSessionId, userId, RshStatus.Run,
                                               DateTime.Now.ToString(CultureInfo.InvariantCulture));

                    if (script.RshId == DM4Consts.DefaultNULLId)
                    {
                        result.Result = DM4Consts.ErrorSQL;
                        result.Message = String.Format("Error db work [run_script_header] script: {0}", script.ScriptName);
                        return result;
                    }
                }
                else
                    GetTaskIdResult(script.RshId, script.Declare.Tasks, rshSessionId);
                #endregion

                if (rshSessionId != null) _runningScripts.Add(rshSessionId, script);

                #region 7. Запуск скрипта на выполнение, только MainTask и в которых статус <> Completed
                //execute all TASK without SUBTASK
                foreach (Prototypes.ScriptTask task in from mainTask in script.Declare.Tasks
                                     where mainTask.Value.Main && mainTask.Value.State != Prototypes.StateTask.Completed && mainTask.Value.TskId == DM4Consts.DefaultNULLId
                                     select mainTask.Value)
                {

                    task.State = Prototypes.StateTask.Runs;

                    task.TskId = InsertTaskToTable(script.RshId, task.TskSessionId, task.TaskName, RshStatus.Run,
                                                DateTime.Now.ToString(CultureInfo.InvariantCulture),
                                                StringUtils.ParamsToString(task.Params));

                    if (task.TskId == -1)
                    {
                        result.Result = DM4Consts.ErrorSQL;
                        result.Message = String.Format("Error insert task in [tasks] : {0}", task.TaskName);
                        return result;
                    }
                }
                #endregion

                #region 8. Запуск task на исполнение
                foreach (Prototypes.ScriptTask task in from mainTask in script.Declare.Tasks
                                     where mainTask.Value.Main && mainTask.Value.State != Prototypes.StateTask.Completed
                                     select mainTask.Value)
                    DoTask(task, null);
                #endregion

                #region 9. Проверка и запуск условий секции ON
                RunTaskOn(script, null, null);
                #endregion

                result.Result = DM4Consts.Ok;
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            //throw new NotImplementedException();

            EndFunction("RunScript");
            return result;
        }

        public OperationResult RunTaskOn(string usrSessionId, ScriptParse scriptParse, string taskName, Dictionary<string, ParamEntry> retParameters)
        {
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = RunTaskOn(scriptParse, taskName, retParameters);
            result.UsrSessionId = userIdOpResult.UsrSessionId;

            return result;
        }

        private OperationResult RunTaskOn(ScriptParse scriptParse, string taskName, Dictionary<string, ParamEntry> retParameters)
        {
            BeginFunction("RUNTaskOn");

            OperationResult result = new OperationResult();

            #region сохранение результатов выполнения таска
            if (!String.IsNullOrEmpty(taskName))
            {
                taskName = taskName.ToUpperInvariant();
                if (scriptParse.Declare.Tasks.ContainsKey(taskName))
                {
                    Prototypes.ScriptTask scriptTask = scriptParse.Declare.Tasks[taskName];
                    scriptTask.Result = retParameters;
                    scriptTask.State = Prototypes.StateTask.Completed;

                    if (!(UpdateTasks(scriptTask.TskId, CnTask.Status, RshStatus.Complete) &&
                          UpdateTasks(scriptTask.TskId, CnTask.Stop, DateTime.Now.ToString(CultureInfo.InvariantCulture)) &&
                          (scriptTask.Result == null || UpdateTasks(scriptTask.TskId, CnTask.Result, StringUtils.ParamsToString(scriptTask.Result)))))   
                    {
                        result.Result = DM4Consts.ErrorSQL;
                        result.Message = String.Format("Error update ScriptTask in [tasks] : {0}", scriptTask.TaskName);
                        return result;
                    }
                }
                else
                {
                    result.Result = DM4Consts.ErrorWorkScript;
                    result.Message = String.Format("ScriptTask name not found: {0}", taskName);
                    return result;
                }
            }
            #endregion

            #region проверка секции скрипта 'ON' и запуск на выполнение
            foreach (Prototypes.TaskRun taskRun in from taskRunForExec in scriptParse.RunSection.TasksRun
                                    where taskRunForExec.State == Prototypes.StateOnTask.NotStarted
                                    select taskRunForExec)
            {
                //если все таски в секции completed
                if (taskRun.TasksOn.All(task => task.Value.State == Prototypes.StateTask.Completed))
                {
                    try
                    {
                        if(!taskRun.SetResult()) throw new Exception();//заполняем результатом условия
                    }
                    catch (Exception)
                    {
                        result.Result = DM4Consts.ErrorWorkScript;
                        result.Message = STR_FailedToSetPropertyInTaskOn;
                        return result;
                    }

                    Queue<Prototypes.ExprAst> run = taskRun.Execute(); //выполняем и получаем список задач для запуска

                    //формирование результата по секции 'ON'
                    Dictionary<string, Dictionary<string, ParamEntry>> parentParams = (from taskOn in taskRun.TasksOn select taskOn.Value).ToDictionary(task => task.TaskName, task => task.Result);

                    //выполнение
                    foreach (Prototypes.ExprAst exprAst in run)
                    {
                        if (exprAst is Prototypes.Command)
                        {
                            //COMMAND RUN ((Prototypes.Command)exprAST).CommandName));
                            result.Message = String.Format("Run command: {0}", ((Prototypes.Command)exprAst).CommandName);
                        }
                        else if (exprAst is Prototypes.ScriptTask)
                        {
                            Prototypes.ScriptTask task = (Prototypes.ScriptTask)exprAst;

                            switch (task.State)
                            {
                                case Prototypes.StateTask.Completed:
                                    //warning: {0}[{1}] is completed",task.ModuleName,task.TaskName));
                                    break;
                                case Prototypes.StateTask.Runs:

                                    result.Message = String.Format("warning : {0}{1} is Working", task.ModuleName,
                                                                   task.TaskName);

                                    //------------------------------------------------
                                    DoTask(task, parentParams);
                                    //------------------------------------------------

                                    break;
                                default:

                                    task.State = Prototypes.StateTask.Runs;
                                    task.TskId = InsertTaskToTable(scriptParse.RshId, task.TskSessionId, task.TaskName, RshStatus.Run,
                                                                DateTime.Now.ToString(CultureInfo.InvariantCulture),
                                                                String.Join("@",
                                                                            from var1 in task.Params
                                                                            select
                                                                                String.Format("{0}%{1}", var1.Key,
                                                                                              var1.Value)));
                                    if (task.TskId == -1)
                                    {
                                        result.Result = DM4Consts.ErrorUnknown;
                                        result.Message = String.Format("Error insert task in [tasks] : {0}",
                                                                       task.TaskName);
                                        return result;
                                    }

                                    //------------------------------------------------
                                    DoTask(task, parentParams);
                                    //------------------------------------------------

                                    break;
                            }
                        }
                        else
                        {
                            result.Result = DM4Consts.ErrorWorkScript;
                            result.Message = STR_ErrorCommand;
                            return result;
                        }
                    }

                    taskRun.State = Prototypes.StateOnTask.Completed;

                }

                if (!scriptParse.RunSection.TasksRun.Any(task => task.State == Prototypes.StateOnTask.NotStarted))
                {
                    //"Все задачи выполнены!!!!!!!!!");
                    result.Message = STR_AllTasksOnCompleted;
                }
            }
            #endregion

            #region проверка на завершение всех тасков в скрипте
            if (scriptParse.Declare.Tasks.All(task => task.Value.State != Prototypes.StateTask.Runs && task.Value.State != Prototypes.StateTask.Wait))
            {
                //"Все таски закончили выполнение!!!!!!!!!");
                result.Message = STR_AllTasksCompleted;

                if (!(UpdateRunScriptHeader(scriptParse.RshId, "[rsh_status]", RshStatus.Complete) &&
                      UpdateRunScriptHeader(scriptParse.RshId, "[rsh_stop]",
                                            DateTime.Now.ToString(CultureInfo.InvariantCulture))))
                {
                    result.Result = DM4Consts.ErrorSQL;
                    result.Message = STR_ErrorUpdateTableRun_script_header;
                    return result;
                }
            }
            #endregion

            EndFunction("RUNTaskOn");

            return result;
        }


        #region functions for working with tatle [run_script_header]

        private bool UpdateRunScriptHeader(long rshId, string field, string value)
        {
            BeginFunction("UpdateRunScriptHeader");

            bool result = true;
            string update = String.Format("UPDATE [run_script_header] SET {0} = @value WHERE [rsh_id] = @rsh_id", field);

            try
            {
                using (SqlCommand command = new SqlCommand(update, _conn))
                {
                    command.Parameters.AddWithValue("@value", value);
                    command.Parameters.AddWithValue("@rsh_id", rshId);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = false;
            }

            EndFunction("UpdateRunScriptHeader");

            return result;
        }


        private long GetIdRunScriptHeader(string sessionsId)
        {
            BeginFunction("GetIdRunScriptHeader");

            long result = -1;
            const string select = "SELECT [rsh_id] FROM [run_script_header] WITH (nolock) WHERE [rsh_sessions_id] = @rsh_sessions_id";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@rsh_sessions_id", sessionsId);
                    result = (long)command.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetIdRunScriptHeader");

            return result;
        }

        private long InsertRunScriptHeader(string scriptName, string sessionsId, int userId, string status,
                                           string startTime)
        {
            BeginFunction("InsertRunScriptHeader");

            long result = -1;
            const string insert =
                "INSERT INTO [run_script_header]([rsh_sessions_id],[rsh_script_name],[rsh_user_id],[rsh_start],[rsh_status]) VALUES(@rsh_sessions_id,@rsh_script_name,@rsh_user_id,@rsh_start,@rsh_status)";

            try
            {
                using (SqlCommand command = new SqlCommand(insert, _conn))
                {
                    command.Parameters.AddWithValue("@rsh_sessions_id", sessionsId);
                    command.Parameters.AddWithValue("@rsh_script_name", scriptName);
                    command.Parameters.AddWithValue("@rsh_user_id", userId);
                    command.Parameters.AddWithValue("@rsh_start", startTime);
                    command.Parameters.AddWithValue("@rsh_status", status);
                    command.ExecuteNonQuery();
                }

                result = GetIdRunScriptHeader(sessionsId);
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("InsertRunScriptHeader");

            return result;
        }

        public OperationResult UpdateRshStop(long rshId, string rshStop)
        {
            BeginFunction("UpdateRshStop");

            OperationResult result = new OperationResult { Result = DM4Consts.Ok };
            const string insert = "UPDATE [run_script_header] SET [rsh_stop] = @rsh_stop WHERE [rsh_id] = @rsh_id";

            try
            {
                using (SqlCommand command = new SqlCommand(insert, _conn))
                {
                    command.Parameters.AddWithValue("@rsh_stop", rshStop);
                    command.Parameters.AddWithValue("@rsh_id", rshId);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("UpdateRshStop");

            return result;
        }

        private OperationResult UpdateScriptStatus(long rshId)
        {
            BeginFunction("UpdateScriptStatus");

            OperationResult result = new OperationResult { Result = DM4Consts.Ok };

            const string select = "SELECT [tsk_status] FROM [tasks] WITH (nolock) WHERE [tsk_rsh] = @rsh_Id";

            try
            {
                Dictionary<string, int> statusTasks = new Dictionary<string, int>() { { TskStatus.Run, 0 }, { TskStatus.Wait, 0 }, { TskStatus.Complete, 0 } };

                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@rsh_Id", rshId);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string status = SysUtils.ConvertFromDBVal<string>(reader[CnTask.Status]); ;
                            if (statusTasks.ContainsKey(status))
                                statusTasks[status]++;
                            else
                            {
                                result.Result = DM4Consts.ErrorTaskStatus;
                                throw new Exception(String.Format("Task status: {0} is error", status));
                            }
                        }
                    }
                }

                if (statusTasks[TskStatus.Run] > 0)
                    UpdateRunScriptHeader(rshId, CnRunScriptHeader.Status, DM4Consts.SessionRun);
                else if (statusTasks[TskStatus.Wait] > 0)
                    UpdateRunScriptHeader(rshId, CnRunScriptHeader.Status, DM4Consts.SessionWait);
        
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("UpdateScriptStatus");

            return result;
        }

        private Dictionary<string, string> GetTaskFromRunScriptHeader(string sessionsId)
        {
            BeginFunction("GetSessionFromTasks");

            Dictionary<string, string> result = new Dictionary<string, string>();
            string select = @"SELECT [tsk_session_id] ,[tsk_status]
                                FROM [tasks] a, [run_script_header] b
                                WHERE [rsh_sessions_id] = @sessions_Id and [tsk_rsh] = [rsh_id]";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@sessions_Id", sessionsId);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            result.Add(SysUtils.ConvertFromDBVal<string>(reader[CnTask.SessionId]), SysUtils.ConvertFromDBVal<string>(reader[CnTask.Status]));
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = null;
            }

            EndFunction("GetSessionFromTasks");

            return result;
        }
        #endregion functions for working with tatle [run_script_header]

        #region functions for working with tatle [tasks]

        private bool DeleteTask(long rshId)
        {
            BeginFunction("DeleteTask");

            bool result = true;
            string delete = String.Format("DELETE FROM [tasks] WHERE [tsk_rsh] = @tsk_rsh and [tsk_status] <> '{0}'",RshStatus.Complete);

            try
            {
                using (SqlCommand command = new SqlCommand(delete, _conn))
                {
                    command.Parameters.AddWithValue("@tsk_rsh", rshId);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = false;
            }

            EndFunction("DeleteTask");

            return result;
        }

        private string GetTaskIdParam(long rshId, string tskName)
        {
            BeginFunction("GetTaskIdParam");

            string result = "";
            const string select =
                "SELECT [tsk_param] FROM [tasks] WHERE [tsk_rsh] = @tsk_rsh and [tsk_name] = @tsk_name";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@tsk_rsh", rshId);
                    command.Parameters.AddWithValue("@tsk_name", tskName);
                    result = (string)command.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetTaskIdParam");

            return result;
        }

        private bool GetTaskIdResult(long rshId, Dictionary<string, Prototypes.ScriptTask> tasks, string rshSessionId)
        {
            BeginFunction("GetTaskStatusResult");

            //long rshId, Dictionary<string, Prototypes.ScriptTask> tasks
            const string select = "SELECT [tsk_id], [tsk_name], [tsk_session_id], [tsk_status], [tsk_result] FROM [tasks] WHERE [tsk_rsh] = @tsk_rsh";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@tsk_rsh", rshId);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (!tasks.ContainsKey((string)reader[CnTask.Name]))
                                throw (new Exception(String.Format("Function 'GetTaskIdResult', task name {0} not found in script", reader[CnTask.Name])));

                            tasks[(string)reader[CnTask.Name]].RshSessionId = rshSessionId;
                            tasks[(string)reader[CnTask.Name]].TskId = (long)reader[CnTask.Id];
                            tasks[(string)reader[CnTask.Name]].State = (string)reader[CnTask.Status] == TskStatus.Run ? Prototypes.StateTask.Runs : (string)reader[CnTask.Status] == TskStatus.Wait ? Prototypes.StateTask.Wait : Prototypes.StateTask.Completed;
                            tasks[(string)reader[CnTask.Name]].Result = StringUtils.StringToParams(reader[CnTask.Result] is DBNull ? "" : (string)reader[CnTask.Result]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                return false;
            }

            EndFunction("GetTaskStatusResult");

            return true;
        }

        private long GetTaskId(long rshId, string tskName)
        {
            BeginFunction("GetTaskId");

            long result = -1;
            const string select = "SELECT [tsk_id] FROM [tasks] WHERE [tsk_rsh] = @tsk_rsh and [tsk_name] = @tsk_name";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@tsk_rsh", rshId);
                    command.Parameters.AddWithValue("@tsk_name", tskName);
                    result = (long)command.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetTaskId");

            return result;
        }

        private long InsertTaskToTable(long tskRsh, string tskSessionId, string tskName, string tskStatus,
                                       string tskStart, string tskParam)
        {
            BeginFunction("InsertTaskToTable");

            long result = -1;
            const string insert =
                "INSERT INTO [tasks]([tsk_rsh],[tsk_session_id],[tsk_name],[tsk_status],[tsk_start],[tsk_param])VALUES(@tsk_rsh,@tsk_session_id,@tsk_name,@tsk_status,@tsk_start,@tsk_param)";

            try
            {
                using (SqlCommand command = new SqlCommand(insert, _conn))
                {
                    command.Parameters.AddWithValue("@tsk_rsh", tskRsh);
                    command.Parameters.AddWithValue("@tsk_session_id", tskSessionId);
                    command.Parameters.AddWithValue("@tsk_name", tskName);
                    command.Parameters.AddWithValue("@tsk_status", tskStatus);
                    command.Parameters.AddWithValue("@tsk_start", tskStart);
                    command.Parameters.AddWithValue("@tsk_param", tskParam);
                    command.ExecuteNonQuery();

                    result = GetTaskId(tskRsh, tskName);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("InsertTaskToTable");

            return result;
        }

        private bool UpdateTasks(long tskId, string field, string value)
        {
            BeginFunction("UpdateTasks");

            bool result = true;
            string update = String.Format("UPDATE [tasks] SET {0} = @value WHERE [tsk_id] = @tsk_id", field);

            try
            {
                using (SqlCommand command = new SqlCommand(update, _conn))
                {
                    command.Parameters.AddWithValue("@value", value);
                    command.Parameters.AddWithValue("@tsk_id", tskId);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = false;
            }

            EndFunction("UpdateTasks");

            return result;
        }

        private Dictionary<string, string> GetSessionFromTasks(string taskId)
        {
            BeginFunction("GetSessionFromTasks");

            Dictionary<string, string> result = new Dictionary<string, string>();
            string select = "SELECT [sess_id], [sess_status] FROM [sessions] WHERE [sess_task] = @tsk_id";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@tsk_id", taskId);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            result.Add(SysUtils.ConvertFromDBVal<string>(reader[CnSessions.Id]), SysUtils.ConvertFromDBVal<string>(reader[CnSessions.Status]));
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = null;
            }

            EndFunction("GetSessionFromTasks");

            return result;
        }

        #endregion functions for working with tatle [tasks]

        #region functions for working with tatle [scripts]

        private int GetScriptsId(string scr_name)
        {
            BeginFunction("GetScriptsId");

            int result = DM4Consts.DefaultNULLId;
            const string select = "SELECT [scr_id] FROM [scripts] WHERE [scr_name] = @scr_name";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@scr_name", scr_name);
                    result = (int)command.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetScriptsId");

            return result;
        }

        private string GetScriptsNameText(int scrId)
        {
            BeginFunction("GetScriptsNameText");

            string result = "";
            const string select = "SELECT [scr_name]+'@'+[scr_text] FROM [scripts] WHERE [scr_id] = @scr_id";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@scr_id", scrId);
                    result = (string)command.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetScriptsNameText");

            return result;
        }

        public XMLInfo GetScriptList(string usrSessionId)
        {
            BeginFunction("GetScriptList");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);

            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            int userId = userIdOpResult.Result;
            WriteLog(String.Format("Get scripts list for user id [{0}]", userId));

            const string sql = "select scripts.*, u1.usr_fullname as scr_last_modified_name, u2.usr_fullname as scr_author_name  from scripts left outer join users u1 on scr_modified_by = u1.usr_id left outer join users u2 on scr_author = u2.usr_id";

            WriteDebugLog(sql);

            try
            {
                SqlCommand sel = new SqlCommand(sql, _conn);

                using (SqlDataReader reader = sel.ExecuteReader())
                {
                    //if (reader.HasRows)
                    try
                    {
                        WriteLog(String.Format("Get script info list for user id [{0}] operation complete", userId));
                        xi.Load(reader, "scripts");
                        //WriteDebugLog(xi.XSD);
                    }

                    catch (Exception ex)
                    {
                        WriteLog(ex.Message, MessageType.Error);
                    }
                }
            }

            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetScriptList");
            return xi;
        }

        public OperationResult AddScript(string usrSessionId, string name, string description, string text)
        {
            //result:
            //>0   script id

            BeginFunction("AddScript");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            int userId = userIdOpResult.Result;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog("Attemtp to add script...");
            try
            {
                ScriptInfo si = GetScriptInfo(name);

                if (String.IsNullOrEmpty(si.ScrName))
                {
                    //insert script                   
                    string sql = String.Format("insert  into scripts (scr_name, scr_description, scr_text, scr_author, scr_modified_by, scr_version, scr_last_modified) " +
                                        " values ('{0}', '{1}', '{2}', '{3}', '{3}', '{4}', @modifieddate)",
                                        name, description, text, userId, 1);

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.Parameters.Add("@modifieddate", SqlDbType.DateTime);
                        sc.Parameters["@modifieddate"].Value = DateTime.Now;
                        sc.ExecuteNonQuery();
                    }
                    si = GetScriptInfo(name);

                    result.Result = si.ScrId;
                    result.Message = String.Format("Script with name [{0}] added", name);

                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("Script with name [{0}] already exists", name);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("AddScript");
            return result;
        }

        public OperationResult EditScript(string usrSessionId, int scriptId, string name, string description, string text)
        {
            //result:
            //>0 script id            
            BeginFunction("EditScript");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            int userId = userIdOpResult.Result;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attemtp to edit script [{0}]...", scriptId));
            try
            {
                ScriptInfo scheck = GetScriptInfo(name);
                if ((!String.IsNullOrEmpty(scheck.ScrName)) && (scheck.ScrId != scriptId))
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("Script with name [{0}] already exists", name);
                    WriteLog(result.Message, MessageType.Error);
                }
                else
                {
                    ScriptInfo si = GetScriptInfo(scriptId);

                    if (!String.IsNullOrEmpty(si.ScrName))
                    {
                        UserInfo ui = GetUserInfo(userId);
                        if (String.IsNullOrEmpty(ui.UsrLogin))
                        {
                            result.Result = DM4Consts.ErrorUnknownUser;
                            result.Message = String.Format("Unknown user with id [{0}]", userId);
                            WriteLog(result.Message, MessageType.Error);
                        }
                        else
                        {
                            //update script
                            string sql = String.Format("update scripts set scr_name='{0}', scr_description = '{1}', scr_text = '{2}', scr_modified_by = {3}, scr_version = '{4}', scr_last_modified = @modifieddate where scr_id = {5}",
                                                       name, description, text, userId, si.ScrVersion + 1, scriptId);

                            WriteDebugLog(sql);
                            using (SqlCommand sc = new SqlCommand(sql, _conn))
                            {
                                sc.Parameters.Add("@modifieddate", SqlDbType.DateTime);
                                sc.Parameters["@modifieddate"].Value = DateTime.Now;
                                sc.ExecuteNonQuery();
                            }

                            si = GetScriptInfo(name);

                            result.Result = si.ScrId;
                            result.Message = String.Format("Script with id [{0}] modified", scriptId);
                            WriteLog(result.Message);
                        }
                    }
                    else
                    {
                        result.Result = DM4Consts.ErrorUnknownEntity;
                        result.Message = String.Format("Unknown script with id [{0}]", scriptId);
                        WriteLog(result.Message, MessageType.Error);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("EditScript");
            return result;
        }

        public OperationResult DeleteScript(string usrSessionId, int scriptId)
        {
            //result:
            //0  Ok
            BeginFunction("DeleteScript");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            int userId = userIdOpResult.Result;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attemtp to delete script [{0}]...", scriptId));
            try
            {
                ScriptInfo si = GetScriptInfo(scriptId);

                if (!String.IsNullOrEmpty(si.ScrName))
                {
                    UserInfo ui = GetUserInfo(userId);
                    if (String.IsNullOrEmpty(ui.UsrLogin))
                    {
                        result.Result = DM4Consts.ErrorUnknownUser;
                        result.Message = String.Format("Unknown user with id [{0}]", userId);
                        WriteLog(result.Message, MessageType.Error);
                    }
                    else
                    {
                        //delete script
                        string sql = String.Format("delete from scripts where scr_id = {0}", scriptId);

                        WriteDebugLog(sql);
                        using (SqlCommand sc = new SqlCommand(sql, _conn))
                        {
                            sc.ExecuteNonQuery();
                        }

                        result.Result = DM4Consts.Ok;
                        result.Message = String.Format("Script with id [{0}] deleted", scriptId);

                        WriteLog(result.Message);
                    }
                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown script with id [{0}]", scriptId);
                    WriteLog(result.Message, MessageType.Error);
                }

            }

            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("DeleteScript");
            return result;
        }

        #endregion functions for working with tatle [scripts]

        #region functions for working with tatle [session]
        private bool UpdateSession(string sessId, string field, string value)
        {
            BeginFunction("UpdateSession");

            bool result = true;
            string update = String.Format("UPDATE [sessions] SET {0} = @value WHERE UPPER([sess_id]) = @sess_id", field);

            try
            {
                using (SqlCommand command = new SqlCommand(update, _conn))
                {
                    command.Parameters.AddWithValue("@value", value);
                    command.Parameters.AddWithValue("@sess_id", sessId.ToUpper());
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = false;
            }

            EndFunction("UpdateSession");

            return result;
        }

        private bool GetRshIdAndTskIdFromSession(string sessId, out long rshId, out long tskId)
        {
            BeginFunction("GetRshIdAndTskIdFromSession");

            rshId = 0; tskId = 0;

            bool result = true;
            string select = @"SELECT cast([rsh_id] AS nvarchar(20))+','+cast([tsk_id] AS nvarchar(20))
	                            FROM [sessions], [tasks], [run_script_header] 
                                WHERE [sess_task] = [tsk_session_id] AND [tsk_rsh] = [rsh_id] AND UPPER([sess_id]) = @sess_id";

            try
            {
                using (SqlCommand command = new SqlCommand(select, _conn))
                {
                    command.Parameters.AddWithValue("@sess_id", sessId.ToUpper());
                    string resultScalar = (string)command.ExecuteScalar();
                    if (!String.IsNullOrWhiteSpace(resultScalar))
                    {
                        rshId = System.Convert.ToInt64(resultScalar.Split(',')[0]);
                        tskId = System.Convert.ToInt64(resultScalar.Split(',')[1]);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
                result = false;
            }

            EndFunction("GetRshIdAndTskIdFromSession");

            return result;
        }

        #endregion
    }
}
